<?php

namespace app\admin\controller\wx;

use app\common\controller\Backend;
use PHPExcel;
use PHPExcel_IOFactory;
use PHPExcel_Shared_Date;
use PHPExcel_Style;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Border;
use PHPExcel_Style_Fill;
use PHPExcel_Style_NumberFormat;

/**
 * 导出管理
 *
 * @icon fa fa-circle-o
 */
class Wximport extends Backend
{
    /**
     * Wximport模型对象
     * @var \app\admin\model\Wximport
     */
    protected $model = null;
    protected $modelPro = null;
    protected $modelOrderInfo = null;
    protected $modelProduct = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\Wxorder();
        $this->modelOrderInfo = new \app\admin\model\Wxorderinfo();
        $this->modelPro = new \app\admin\model\Wxpro();
        $this->modelProduct = new \app\admin\model\Wxproduct();
        $agentList = $this->model->alias('a')->join('hdyj_agent_info b ','a.agent_id= b.id')->field('a.agent_id,b.agent_name')->select();
        $this->view->assign("agentList", array_column($agentList,'agent_name','agent_id'));
        $this->view->assign("typeList", $this->modelPro->getTypeList());

    }
    
    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */
    

    /**
     * 查看
     */
    public function index()
    {
        //当前是否为关联查询
        $this->relationSearch = true;
        $this->searchFields = 'info.agent_name';
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax())
        {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField'))
            {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $total = $this->model
                    ->with(['info'])
                    ->where($where)
                    ->order($sort, $order)
                    ->group('agent_id')
                    ->count();
            $list = $this->model
                    ->with(['info'])
                    ->where($where)
                    ->order($sort, $order)
                    ->limit($offset, $limit)
                    ->group('agent_id')
                    ->select();
            foreach ($list as $row) {
                $row->visible(['id','agent_id']);
                $row->visible(['info']);
                $row->getRelation('info')->visible(['agent_name']);
            }
            $list = collection($list)->toArray();
            $result = array("total" => $total, "rows" => $list);

            return json($result);
        }
        return $this->view->fetch();
    }

    /**
     * 查看
     */
    public function index2()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax())
        {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField'))
            {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $order_sn = $this->model
                ->where($where)
                ->order($sort, $order)
                ->field('group_concat(order_sn) as order_sns,xdtime,status,state')->find()->order_sns;
            $list = collection($this->modelOrderInfo->where('order_sn','in',$order_sn)->field('sum(num) as num,wxproduct_id as id')->group('wxproduct_id')->select())->toArray();
            $data = [];
            foreach ($list as &$value)
            {
                $row = $this->modelProduct->get($value['id']);
                if($row->wxpro_ids)
                {
                    $value['wxpro_ids'] = $row->wxpro_ids;
                    foreach (explode(',',$row->wxpro_ids) as $va)
                    {
                        $r = $this->modelPro->get($va);
                        if($r){
                            if(!isset($data[$r->id]))
                            {
                                $data[$r->id] = [
                                    'id'=>$r->id,
                                    'num'=>$value['num'],
                                    'name'=>$r->name,
                                    'type_text'=>$r->type_text,
                                    'type'=>$r->type
                                ];
                            }else{
                                $data[$r->id]['num'] += $value['num'];
                            }
                        }
                    }
                }
            }
            $data = $this->arraySort($data,'num');//排序
            $result = array("total" => count($data), "rows" => array_slice($data,$offset,$limit));
            return json($result);
        }
        return $this->view->fetch();
    }

    /**
     * 二维数组根据某个字段排序
     * @param array $array 要排序的数组
     * @param string $keys   要排序的键字段
     * @param string $sort  排序类型  SORT_ASC     SORT_DESC
     * @return array 排序后的数组
     */
    protected  function arraySort($array, $keys, $sort = SORT_DESC) {
        $keysValue = [];
        foreach ($array as $k => $v) {
            $keysValue[$k] = $v[$keys];
        }
        array_multisort($keysValue, $sort, $array);
        return $array;
    }
    /**
     * 财务导出
     * @param null $ids
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     * @throws \think\exception\DbException
     */
    public function cwexport($ids = null)
    {
        $createtime = date('Y-m-d H:i:s',time());
        $row = $this->model->get($ids);
        $row->cw += 1;
        /**
         * B表的 单据日期  单据编号  金额
           C表的 客户编号
           B表的 姓名 电话 地址
           D表的 存货编码  码洋
           A表的  数量
           业务类型编码15
         */
        $list = $this->modelOrderInfo
            ->alias('a')
            ->join('hdyj_wxorder b','a.order_sn = b.order_sn')
            ->join('hdyj_agent_info c','b.agent_id = c.id')
            ->join('hdyj_wxproduct d','a.wxproduct_id = d.id')
            ->where('b.id','in',$row->ids)
            ->where('b.state = 1 and (b.status =0  or b.status = 1 or b.status = 3)')//审核通过的  财务需要的数据  原始数据  被拆分的  被合并的
            ->field('a.num as num,a.lts as lts,d.wxpro_ids,a.tax as tax,b.receipt as name,d.code as code,b.phone as phone,b.address as address,d.type as type,d.unit as unit,d.ministrial as ministrial,b.xdtime as xdtime,b.order_sn as order_sn,b.amount,c.finance_code as finance_code,c.discount_array as discount_array')
            ->select();
        $list = collection($list)->toArray();
//        echo $this->modelOrderInfo->getLastSql();exit;
        if(count($list) == 0)
        {
            $this->error('没有可导出的数据！','/admin/wx/wximport?ref=addtabs');
        }
        $orderimport = [];
        foreach ($list as $item)
        {
            $data = collection($this->modelPro->where('id','in',$item['wxpro_ids'])->field('code,type,name,fenlei,kemu,banben')->select())->toArray();
            unset($item['wxpro_ids']);
            foreach ($data as $val)
            {
                if(!$ministrial = $this->modelProduct->get(['name'=>$val['name']])->ministrial)
                {
                    $this->error("此成品{$val['name']}的码洋没有",'/admin/wx/wximport?ref=addtabs');
                }
                $item['ministrial'] = $ministrial;
                if($item['lts'] < 1)
                {
                    $item['tax'] = $item['ministrial'] * $item['lts'];
                }else{
                    //（没折扣）订单总价 / 取所有商品码洋总价  = 比例 ）    （每一类商品总价 * 折扣 = 含税金额）
                    $mayang = $this->modelOrderInfo->where('order_sn',$item['order_sn'])->sum('price');
                    $item['lts'] = $item['amount'] / $mayang;
                    $item['tax'] = $item['lts'] * $item['ministrial'];
                }
                $item['code'] = $val['code'];
                $item['pro_name'] = $val['name'];
                $orderimport[] = $item;
            }
        }

        $excel = new PHPExcel();

        $excel->getProperties()
            ->setCreator("YangLiu")
            ->setLastModifiedBy("YangLiu")
            ->setTitle("销售订单")
            ->setSubject("Subject");
        $excel->getDefaultStyle()->getFont()->setName('Microsoft Yahei');
        $excel->getDefaultStyle()->getFont()->setSize(12);

        $this->sharedStyle = new PHPExcel_Style();
        $this->sharedStyle->applyFromArray(
            array(
                'fill'      => array(
                    'type'  => PHPExcel_Style_Fill::FILL_SOLID,
                    'color' => array('rgb' => '000000')
                ),
                'font'      => array(
                    'color' => array('rgb' => "000000"),
                ),
                'alignment' => array(
                    'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'indent'     => 1
                ),
                'borders'   => array(
                    'allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                )
            ));

        $worksheet = $excel->setActiveSheetIndex(0);

        $worksheet->setTitle('标题');

        $worksheet->setCellValue("A1", "单据日期");
        $worksheet->setCellValue("B1", "单据编号");
        $worksheet->setCellValue("C1", "业务类型编码");
        $worksheet->setCellValue("D1", "业务类型");
        $worksheet->setCellValue("E1", "售后类型");
        $worksheet->setCellValue("F1", "客户编码");
        $worksheet->setCellValue("G1", "客户");
        $worksheet->setCellValue("H1", "结算客户编码");
        $worksheet->setCellValue("I1", "结算客户");
        $worksheet->setCellValue("J1", "部门编码");
        $worksheet->setCellValue("K1", "部门");
        $worksheet->setCellValue("L1", "业务员编码");
        $worksheet->setCellValue("M1", "业务员");
        $worksheet->setCellValue("N1", "运输方式");
        $worksheet->setCellValue("O1", "送货地址");
        $worksheet->setCellValue("P1", "联系人");
        $worksheet->setCellValue("Q1", "联系电话");
        $worksheet->setCellValue("R1", "客户手机号");
        $worksheet->setCellValue("S1", "合同号");
        $worksheet->setCellValue("T1", "收款方式");
        $worksheet->setCellValue("U1", "外部单据号");
        $worksheet->setCellValue("V1", "按仓库拆单出库");
        $worksheet->setCellValue("W1", "备货周次");
        $worksheet->setCellValue("X1", "收货方姓名");
        $worksheet->setCellValue("Y1", "收货方电话");
        $worksheet->setCellValue("Z1", "收货方地址");
        $worksheet->setCellValue("AA1", "收货方式");
        $worksheet->setCellValue("AB1", "周次");
        $worksheet->setCellValue("AC1", "场次");
        $worksheet->setCellValue("AD1", "终端客户");
        $worksheet->setCellValue("AE1", "备注");
        $worksheet->setCellValue("AF1", "仓库编码");
        $worksheet->setCellValue("AG1", "仓库");
        $worksheet->setCellValue("AH1", "项目编码");
        $worksheet->setCellValue("AI1", "项目");
        $worksheet->setCellValue("AJ1", "客户存货名称");
        $worksheet->setCellValue("AK1", "客户存货编码");
        $worksheet->setCellValue("AL1", "存货编码");
        $worksheet->setCellValue("AM1", "存货名称");
        $worksheet->setCellValue("AN1", "适用BOM");
        $worksheet->setCellValue("AO1", "销售单位");
        $worksheet->setCellValue("AP1", "数量");
        $worksheet->setCellValue("AQ1", "报价");
        $worksheet->setCellValue("AR1", "折扣%");
        $worksheet->setCellValue("AS1", "税率");
        $worksheet->setCellValue("AT1", "含税单价");
        $worksheet->setCellValue("AU1", "含税金额");
        array_walk($orderimport,function ($value,$k) use ($worksheet){
            /**
             *  数量* 码洋 = 报价
             *  （没折扣）订单总价 *  取码洋总价  = 比例 ）    （每一类商品总价 * 折扣 = 含税金额）
             *  (取折扣)码洋 * 折扣 = 含税金额
             */
            $Key = $k+2;
            $date = date('Y/m/d',$value['xdtime']);
            $worksheet->setCellValue("A".$Key, $date);//下单日期
            $worksheet->setCellValue("B".$Key, ' '.$value['order_sn'].' ');//订单号
            $worksheet->setCellValue("C".$Key, 15);//默认15
            $worksheet->setCellValue("F".$Key, ' '.$value['finance_code'].' ');//客户对应财务的编码

            $worksheet->setCellValue("X".$Key, $value['name']);//收货人名称
            $worksheet->setCellValue("Y".$Key, $value['phone']);//收货人电话
            $worksheet->setCellValue("Z".$Key, $value['address']);//收货人地址

            $worksheet->setCellValue("AL".$Key, ' '.$value['code'].' ');//存货编码
            $worksheet->setCellValue("AM".$Key, $value['pro_name']);//存货名称
            $worksheet->setCellValue("AP".$Key, $value['num']);//数量
            $worksheet->setCellValue("AQ".$Key, $value['ministrial']);//码洋
            $worksheet->setCellValue("AR".$Key, ($value['lts'] * 100)."%");//折扣
            $worksheet->setCellValue("AU".$Key, $value['tax'] * $value['num']);//含税金额
            $worksheet->getColumnDimension('A')->setWidth(15); //设置列宽
            $worksheet->getColumnDimension('B')->setWidth(30); //设置列宽
            $worksheet->getColumnDimension('C')->setWidth(10); //设置列宽
            $worksheet->getColumnDimension('F')->setWidth(15); //设置列宽
            $worksheet->getColumnDimension('Y')->setWidth(15); //设置列宽
            $worksheet->getColumnDimension('Z')->setWidth(35); //设置列宽
            $worksheet->getColumnDimension('AL')->setWidth(15); //设置列宽
            $worksheet->getColumnDimension('AM')->setWidth(20); //设置列宽
        });
        unset($list);
        $excel->createSheet();
        $endtime = date('Y-m-d H:i:s',time());
        \app\admin\model\Wxorder::msg('cwdc',$this->auth->username,$createtime,$endtime,'',$row->cw);
        unset($createtime,$endtime);
        // Redirect output to a client’s web browser (Excel2007)
        $title = '财务导出'.date("YmdHis");
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
        $objWriter->save('php://output');
        $row->save();
        return;

    }

    /**
     * 物流导出
     * @param $ids
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     * @throws \think\exception\DbException
     */
    public function wlexport($ids)
    {
        $createtime = date('Y-m-d H:i:s',time());
        $row = $this->model->get($ids);
        $row->wl += 1;
        $list = collection($this->modelOrder->where('id','in',$row->ids)->where('status = 2 or status = 4 or status = 0 and state = 1')->order('weigh desc')->select())->toArray();
        if(count($list) == 0)
        {
            $this->error('没有要导出的订单！');
        }
        $excel = new PHPExcel();
        $excel->getProperties()
            ->setCreator("YangLiu")
            ->setLastModifiedBy("YangLiu")
            ->setTitle("销售订单")
            ->setSubject("Subject");
        $excel->getDefaultStyle()->getFont()->setName('Microsoft Yahei');
        $excel->getDefaultStyle()->getFont()->setSize(12);

        $this->sharedStyle = new PHPExcel_Style();
        $this->sharedStyle->applyFromArray(
            array(
                'fill'      => array(
                    'type'  => PHPExcel_Style_Fill::FILL_SOLID,
                    'color' => array('rgb' => '000000')
                ),
                'font'      => array(
                    'color' => array('rgb' => "000000"),
                ),
                'alignment' => array(
                    'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'indent'     => 1
                ),
                'borders'   => array(
                    'allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                )
            ));

        $worksheet = $excel->setActiveSheetIndex(0);

        $worksheet->setTitle('物流导出');

        $worksheet->setCellValue('A1', '排序');
        $worksheet->setCellValue('B1', '下单日期');
        $worksheet->setCellValue('C1', '订单编号');
        $worksheet->setCellValue('D1', '代理商ID');
        $worksheet->setCellValue('E1', '版本');
        $worksheet->setCellValue('F1', 'A学习书');
        $worksheet->setCellValue('G1', 'B练习书');
        $worksheet->setCellValue('H1', 'C素材书');
        $worksheet->setCellValue('I1', '字词句手册');
        $worksheet->setCellValue('J1', '年级阅读');
        $worksheet->setCellValue('K1', '教案');
        $worksheet->setCellValue('L1', '数学');
        $worksheet->setCellValue('M1', '收件人');
        $worksheet->setCellValue('N1', '收件人电话');
        $worksheet->setCellValue('O1', '收件人地址');
        $worksheet->setCellValue('P1', '快递方式');
        $worksheet->setCellValue('Q1', '快递单号');
        $worksheet->setCellValue('R1', '备注');
        $worksheet->setCellValue('S1', '缺货');
        $worksheet->setCellValue('T1', '补货');
        //去除所有空格
        array_walk($list,function (&$value,$k) use ($worksheet,$excel){
            $value['goods_name'] = '';
            $data = collection($this->modelOrderInfo->where('order_sn',$value['order_sn'])->field('goods_name,sum(num) as nums,dk')->group('wxproduct_id')->select())->toArray();
            array_walk($data,function ($data,$key) use(&$value){
                if($data['dk'])
                {
                    $data['dk'] = "({$data['dk']})";
                    return $value['goods_name'] .= $data['goods_name'].=$data['dk'] .='['.$data['nums']."]";
                }
                return $value['goods_name'] .= $data['goods_name'] .='['.$data['nums']."]";
            });
            unset($value['status_text'],$value['state'],$value['status'],$value['updatetime'],$data);
            $Key = $k+2;
            $worksheet->setCellValue("A".$Key, $value['weigh']);//排序
            $worksheet->setCellValue("B".$Key, $value['xdtime_text']);//下单日期
            $worksheet->setCellValue("C".$Key, " ".$value['order_sn']." ");//订单号
            $worksheet->setCellValue("D".$Key, $value['agent_id']);//代理商ID
            $worksheet->setCellValue("E".$Key, $value['goods_name']);//商品名称详情
            $worksheet->setCellValue("M".$Key, $value['receipt']);//收货人名称
            $worksheet->setCellValue("N".$Key, trim($value['phone']));//收货人电话
            $address = str_replace(' ', '', $value['address']);
            $worksheet->setCellValue("O".$Key, $address);//收货人地址
            $worksheet->setCellValue("P".$Key, $value['deliveryForm']);//快递方式
            $worksheet->setCellValue("R".$Key, $value['import_note']);//导入备注

            $worksheet = $excel->getActiveSheet();
            $worksheet->getColumnDimension('A')->setWidth(10); //设置列宽
            $worksheet->getColumnDimension('B')->setWidth(20);
            $worksheet->getColumnDimension('C')->setWidth(20);
            $worksheet->getColumnDimension('D')->setWidth(10);
            $worksheet->getColumnDimension('E')->setWidth(40);
            $worksheet->getColumnDimension('F')->setWidth(15);
            $worksheet->getColumnDimension('G')->setWidth(20);
            $worksheet->getColumnDimension('H')->setWidth(20);
            $worksheet->getColumnDimension('I')->setWidth(20);
            $worksheet->getColumnDimension('J')->setWidth(20);
            $worksheet->getColumnDimension('K')->setWidth(15);
            $worksheet->getColumnDimension('L')->setWidth(15);
            $worksheet->getColumnDimension('M')->setWidth(15);
            $worksheet->getColumnDimension('N')->setWidth(15);
            $worksheet->getColumnDimension('O')->setWidth(45);
            $worksheet->getColumnDimension('P')->setWidth(15);
            $worksheet->getColumnDimension('Q')->setWidth(15);
            $worksheet->getRowDimension($Key)->setRowHeight(17); //设置行高
        });
        $excel->createSheet();
        // Redirect output to a client’s web browser (Excel2007)
        $title = '物流导出'.date("YmdHis");
        $endtime = date('Y-m-d H:i:s',time());
        \app\admin\model\Wxorder::msg('wldc',$this->auth->username,$createtime,$endtime,'',$row->wl);
        unset($createtime,$endtime);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
        $objWriter->save('php://output');
        $row->save();
        return;
    }

    public function wxpro($ids)
    {
        $createtime = date('Y-m-d H:i:s',time());
        $row = $this->model->get($ids);
        $row->cw += 1;
        $order_sn = $this->modelOrder->where('id','in',$row->ids)->where('state = 1 and (status =0  or status = 1 or status = 3)')->field('group_concat(order_sn) as order_sn')->find()->order_sn;
        $list = collection($this->modelOrderInfo
            ->alias('a')
            ->join('hdyj_wxproduct b','a.wxproduct_id = b.id')
            ->where('a.order_sn','in',$order_sn)
            ->field('b.name,a.dk,a.num,b.wxpro_ids,a.order_sn,b.id')
            ->select()
        )->toArray();
        $proData = [];
        foreach ($list as $value)
        {
            //是否多选课 获取多科的半产品ID
            if($value['dk'])
            {
                $value['wxpro_ids'] = '';
                foreach (explode(',',$value['dk']) as $dkval)
                {
                    $name = str_replace('全套',$dkval,$value['name']);
                    if(!$rows = $this->modelPro->where('name','like',"%{$name}%")->find())
                    {
                        $str = "订单号:  {$value['order_sn']}";
                        $str .= "文件:/application/admin/controller/wx/Wximpor.php 423行";
                        $str .= "没有找到半成品数据:{$value['name']}{$dkval}";
                        $this->wxmsg($str);
                        unset($str);
                        $this->error('没有找到半成品数据'.$value['name'].$dkval);
                    }
                    $value['wxpro_ids'] .=  $rows->id.",";
                }
            }
            //查询办成品
            $data = collection($this->modelPro->where('id','in',$value['wxpro_ids'])->field('name,type,code')->select())->toArray();
            if(!$data)
            {
                $str = "订单号:  {$value['order_sn']}";
                $str .= "文件:/application/admin/controller/wx/Wximpor.php 436行";
                $str .= "没有添加半成品数据:{$value['name']} wxproductID：{$value['id']}";
                $this->wxmsg($str);
                unset($str);
                $this->error('没有找到半成品数据'.$value['name'].$dkval);
            }
            foreach ($data as $values)
            {
                $values['num'] = $value['num'];
                unset($values['type'],$values['type_text']);
                $proData[] = $values;
            }
        }

        $excel = new PHPExcel();

        $excel->getProperties()
            ->setCreator("YangLiu")
            ->setLastModifiedBy("YangLiu")
            ->setTitle("半成品详情")
            ->setSubject("Subject");
        $excel->getDefaultStyle()->getFont()->setName('Microsoft Yahei');
        $excel->getDefaultStyle()->getFont()->setSize(12);

        $this->sharedStyle = new PHPExcel_Style();
        $this->sharedStyle->applyFromArray(
            array(
                'fill'      => array(
                    'type'  => PHPExcel_Style_Fill::FILL_SOLID,
                    'color' => array('rgb' => '000000')
                ),
                'font'      => array(
                    'color' => array('rgb' => "000000"),
                ),
                'alignment' => array(
                    'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'indent'     => 1
                ),
                'borders'   => array(
                    'allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN),
                )
            ));
        $worksheet = $excel->setActiveSheetIndex(0);

        $worksheet->setTitle('标题');

        $worksheet->setCellValue("A1", "产品编号");
        $worksheet->setCellValue("B1", "名称");
        $worksheet->setCellValue("C1", "数量");
        array_walk($proData,function ($value,$k) use ($worksheet){
            $key = $k+2;
            $worksheet->setCellValue("A".$key, $value['code']);//商品编号
            $worksheet->setCellValue("B".$key, $value['name']);//名称
            $worksheet->setCellValue("C".$key, $value['num']);//数量
            $worksheet->getColumnDimension('A')->setWidth(10); //设置列宽
            $worksheet->getColumnDimension('B')->setWidth(30); //设置列宽
            $worksheet->getColumnDimension('C')->setWidth(6); //设置列宽
        });
        unset($proData);
        $excel->createSheet();
        $endtime = date('Y-m-d H:i:s',time());
        \app\admin\model\Wxorder::msg('cwdc',$this->auth->username,$createtime,$endtime,'',$row->cw);
        unset($createtime,$endtime);
        // Redirect output to a client’s web browser (Excel2007)
        $title = '财务导出半成品'.date("YmdHis");
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
        $objWriter->save('php://output');
        $row->save();
        return;
    }

    public function getPro($agent_id = null, $time = null,$offset = 0,$limit = 0)
    {
        $where = ['courierBut'=>['<>',' ']];//已发货的
        if($agent_id)
        {
            $where['agent_id'] = $agent_id;
        }
        if($time)
        {
            $where['updatetime'] =  ["BETWEEN", [strtotime($time[0]),strtotime($time[1])]];;
        }
        $order_sn =  $this->model->where($where)->field('group_concat(order_sn) as order_sns')->find()->order_sns;

        if(!$order_sn) return [0,[]];
        $list = collection($this->modelOrderInfo->where('order_sn','in',$order_sn)->field('sum(num) as num,wxproduct_id as id')->group('wxproduct_id')->select())->toArray();
        $data = [];
        foreach ($list as &$value)
        {
            $row = $this->modelProduct->get($value['id']);
            if($row->wxpro_ids)
            {
                $value['wxpro_ids'] = $row->wxpro_ids;
                foreach (explode(',',$row->wxpro_ids) as $va)
                {
                    $r = $this->modelPro->get($va);
                    $data[] = [
                        'num'=>$value['num'],
                        'name'=>$r->name,
                        'type_text'=>$r->type_text,
                        'type'=>$r->type
                    ];
                }
            }
        }
        return [count($data),array_slice($data,$offset,$limit)];
    }

    /**
     * 生成查询所需要的条件,排序方式
     * @param mixed $searchfields 快速查询的字段
     * @param boolean $relationSearch 是否关联查询
     * @return array
     */
    protected function buildparams($searchfields = null, $relationSearch = null)
    {
        $searchfields = is_null($searchfields) ? $this->searchFields : $searchfields;
        $relationSearch = is_null($relationSearch) ? $this->relationSearch : $relationSearch;
        $search = $this->request->get("search", '');
        $filter = $this->request->get("filter", '');
        $op = $this->request->get("op", '', 'trim');
        $sort = $this->request->get("sort", "id");
        $order = $this->request->get("order", "DESC");
        $offset = $this->request->get("offset", 0);
        $limit = $this->request->get("limit", 0);
        $filter = (array)json_decode($filter, TRUE);
        $op = (array)json_decode($op, TRUE);
        $filter = $filter ? $filter : [];
        $where = [];
        $tableName = '';
        if ($relationSearch) {
            if (!empty($this->model)) {
                $name = \think\Loader::parseName(basename(str_replace('\\', '/', get_class($this->model))));
                $tableName = $name . '.';
            }
            $sortArr = explode(',', $sort);
            foreach ($sortArr as $index => & $item) {
                $item = stripos($item, ".") === false ? $tableName . trim($item) : $item;
            }
            unset($item);
            $sort = implode(',', $sortArr);
        }
        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds)) {
            $where[] = [$tableName . $this->dataLimitField, 'in', $adminIds];
        }
        if ($search) {
            $searcharr = is_array($searchfields) ? $searchfields : explode(',', $searchfields);
            foreach ($searcharr as $k => &$v) {
                $v = stripos($v, ".") === false ? $tableName . $v : $v;
            }
            unset($v);
            $where[] = [implode("|", $searcharr), "LIKE", "%{$search}%"];
        }
        foreach ($filter as $k => $v) {
            $sym = isset($op[$k]) ? $op[$k] : '=';
            if (stripos($k, ".") === false) {
                $k = $tableName . $k;
            }
            $v = !is_array($v) ? trim($v) : $v;
            $sym = strtoupper(isset($op[$k]) ? $op[$k] : $sym);
            switch ($sym) {
                case '=':
                case '!=':
                    $where[] = [$k, $sym, (string)$v];
                    break;
                case 'LIKE':
                case 'NOT LIKE':
                case 'LIKE %...%':
                case 'NOT LIKE %...%':
                    $where[] = [$k, trim(str_replace('%...%', '', $sym)), "%{$v}%"];
                    break;
                case '>':
                case '>=':
                case '<':
                case '<=':
                    $where[] = [$k, $sym, intval($v)];
                    break;
                case 'FINDIN':
                case 'FINDINSET':
                    $where[] = "FIND_IN_SET('{$v}', " . ($this->relationSearch ? $k : '`' . str_replace('.', '`.`', $k) . '`') . ")";
                    break;
                case 'FIND_IN_SET':
                    $where[] = "FIND_IN_SET( " . ($this->relationSearch ? $k : '`' . str_replace('.', '`.`', $k) . '`') . ",'{$v}')";
                    break;
                case 'IN':
                case 'IN(...)':
                case 'NOT IN':
                case 'NOT IN(...)':
                    $where[] = [$k, str_replace('(...)', '', $sym), is_array($v) ? $v : explode(',', $v)];
                    break;
                case 'BETWEEN':
                case 'NOT BETWEEN':
                    $arr = array_slice(explode(',', $v), 0, 2);
                    if (stripos($v, ',') === false || !array_filter($arr))
                        continue;
                    //当出现一边为空时改变操作符
                    if ($arr[0] === '') {
                        $sym = $sym == 'BETWEEN' ? '<=' : '>';
                        $arr = $arr[1];
                    } else if ($arr[1] === '') {
                        $sym = $sym == 'BETWEEN' ? '>=' : '<';
                        $arr = $arr[0];
                    }
                    $where[] = [$k, $sym, $arr];
                    break;
                case 'RANGE':
                case 'NOT RANGE':
                    $v = str_replace(' - ', ',', $v);
                    $arr = array_slice(explode(',', $v), 0, 2);
                    if (stripos($v, ',') === false || !array_filter($arr))
                        continue;
                    //当出现一边为空时改变操作符
                    if ($arr[0] === '') {
                        $sym = $sym == 'RANGE' ? '<=' : '>';
                        $arr = $arr[1];
                    } else if ($arr[1] === '') {
                        $sym = $sym == 'RANGE' ? '>=' : '<';
                        $arr = $arr[0];
                    }
                    $where[] = [$k, str_replace('RANGE', 'BETWEEN', $sym) . ' time', $arr];
                    break;
                case 'LIKE':
                case 'LIKE %...%':
                    $where[] = [$k, 'LIKE', "%{$v}%"];
                    break;
                case 'NULL':
                case 'IS NULL':
                case 'NOT NULL':
                case 'IS NOT NULL':
                    $where[] = [$k, strtolower(str_replace('IS ', '', $sym))];
                    break;
                default:
                    break;
            }
        }
        $where = function ($query) use ($where) {
            foreach ($where as $k => $v) {
                if (is_array($v)) {
                    call_user_func_array([$query, 'where'], $v);
                } else {
                    $query->where($v);
                }
            }
        };
        return [$where, $sort, $order, $offset, $limit];
    }


}
